package tk.mybatis.springboot.mapper;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import tk.mybatis.springboot.model.*;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

public interface UserMapper {

    @Select("select *,open_id as openId  ,phone_number as phoneNumber from students where  open_id = #{code}")
    Students getByOpenId(String cope);

    @Select("select *,open_id as openId ,phone_number as phoneNumber from students where  id = #{id}")
    Students getById(String id);


    @Select("select * fromm students")
    List<Students> getAllStudents();

    List<Students> getStudents(StudentsForm students);

    Integer insertUser(Students students);

    Integer updateUser(Students students);

    @Select("select id,name from class where id = #{id}")
    HashMap<String,String> getCourseClass(String id);

    @Select("select id,name from large_group where class_id =#{id} limit 1")
    HashMap<String,String> getLargeGroup(String id);


    @Select("select id,name from small_group where large_group_id =#{id} limit 1")
    HashMap<String,String> getSmallGroup(String id);


    @Select("SELECT " +
            "    student_id AS studentId," +
            "    course_id AS courseId," +
            "    course_name AS courseName," +
            "    current_session_number AS currentSessionNumber," +
            "    class_id AS classId," +
            "    class_name AS className," +
            "    large_group_id AS largeGroupId," +
            "    large_group_name AS largeGroupName," +
            "    small_group_id AS smallGroupId," +
            "    small_group_name AS smallGroupName" +
            " FROM " +
            "    student_course " +
            "    where student_id = #{id} and class_id not in (select class_id from certificate where student_id = #{id})")
    List<StudentCourse> getClassAndGroup(String id);


    @Select("SELECT " +
            "    student_id AS studentId," +
            "    course_id AS courseId," +
            "    course_name AS courseName," +
            "    current_session_number AS currentSessionNumber," +
            "    class_id AS classId," +
            "    class_name AS className," +
            "    large_group_id AS largeGroupId," +
            "    large_group_name AS largeGroupName," +
            "    small_group_id AS smallGroupId," +
            "    small_group_name AS smallGroupName" +
            " FROM " +
            "    student_course ")
    List<StudentCourse> getAllStudentCourse();


    @Select("SELECT " +
            "    student_id AS studentId," +
            "    course_id AS courseId," +
            "    course_name AS courseName," +
            "    current_session_number AS currentSessionNumber," +
            "    class_id AS classId," +
            "    class_name AS className," +
            "    large_group_id AS largeGroupId," +
            "    large_group_name AS largeGroupName," +
            "    small_group_id AS smallGroupId," +
            "    small_group_name AS smallGroupName" +
            " FROM " +
            "    student_course " +
            "    where student_id = #{id} ")
    List<StudentCourse> getClassAndGroup2(String id);


    @Select("select homework_tasks from course_homework where course_id = #{id} ")
    String getWorks(String id);

    @Select("select id,name from class where id = #{id} ")
    List<HashMap<String,String>> getResource1(String id);

    @Select("select id,name from large_group where class_id = #{id} ")
    List<HashMap<String,String>> getResource2(String id);

    @Select("select id,name from small_group where large_group_id = #{id} ")
    List<HashMap<String,String>> getResource3(String id);

    int deleteStudentCourseRecord(StudentCourse studentCourse);

    int insertStudentCourseRecord(StudentCourse studentCourse);

    int addAward(AwardRecord awardRecord);

    @Delete("delete from award_records where student_id  = #{studentId} and  award_name  = #{awardName} and small_group_id  = #{smallGroupId} ")
    int deleteAward(String studentId,String awardName,String smallGroupId );

    @Select("SELECT " +
            "student_id AS studentId, " +
            "student_name AS studentName, " +
            "awarder_id AS awarderId, " +
            "award_name AS awardName, " +
            "award_time AS awardTime, " +
            "current_session_number AS currentSessionNumber, " +
            "class_id AS classId, " +
            "class_name AS className, " +
            "large_group_id AS largeGroupId, " +
            "large_group_name AS largeGroupName, " +
            "small_group_id AS smallGroupId, " +
            "small_group_name AS smallGroupName " +
            "FROM award_records where student_id = #{id}")
    List<AwardRecord> getAwardBody1(String id);

    @Select("SELECT " +
            "student_id AS studentId, " +
            "student_name AS studentName, " +
            "awarder_id AS awarderId, " +
            "award_name AS awardName, " +
            "award_time AS awardTime, " +
            "current_session_number AS currentSessionNumber, " +
            "class_id AS classId, " +
            "class_name AS className, " +
            "large_group_id AS largeGroupId, " +
            "large_group_name AS largeGroupName, " +
            "small_group_id AS smallGroupId, " +
            "small_group_name AS smallGroupName " +
            "FROM award_records  where awarder_id = #{id}")
    List<AwardRecord> getAwardBody2(String id);

    @Select("SELECT " +
            "id, " +
            "student_id AS studentId, " +
            "class_id AS classId, " +
            "class_name AS className," +
            "create_time AS createTime, " +
            "name " +
            "FROM certificate " +
            "WHERE student_id = #{id}")
    List<Certificate> getCertificate(String id);

    @Update("update students set face = #{face} where id = #{id}")
    int setFace(String face,String id);

    @Select("select * from homework where session_number =#{sessionNumber} and class_id = #{classId}")
    List<Homework> getHomework(String sessionNumber,String classId);

    @Delete("delete from homework where session_number =#{sessionNumber} and class_id = #{classId}")
    int deleteWork(String sessionNumber,String classId);



    int addWork(Homework homework);

    @Select("select days from student_course where student_id = #{studentId} and class_id = #{classId} limit 1")
    String getClassDays(String studentId,String classId);

    @Select("select id,session_number as sessionNumber,class_id as classId,days,content,title,type from homework where class_id = #{classId} and session_number = #{currentSessionNumber} and days = #{days} ")
    List<Homework> getHomeworkByDays(String classId,String currentSessionNumber,String days);

    @Select("select id,session_number as sessionNumber,class_id as classId,days,content,title,type from homework  where class_id = #{classId} and session_number = #{currentSessionNumber} and  days =#{days} and id not in (select work_id from student_work where student_id = #{studentId})")
    List<Homework> getNoWork(String classId,String currentSessionNumber,String days,String studentId);

    int addHomeWork(HomeworkInfo homeworkInfo);

    @Delete("delete from homework_info where student_id = #{studentId} and work_id = #{workId}")
    int deleteHomework(String studentId, String workId);


    @Delete("delete from student_work where student_id = #{studentId} and work_id = #{workId}")
    int deleteStudentHomework(String studentId, String workId);


    @Insert("insert into student_work(student_id,work_id) values (#{studentId},#{workId})")
    int addStudentHomework(String studentId, String workId);

    @Select("select id,student_id as studentId,work_id as workId,face,name,content,create_time as createTime,like_count as likeCount from homework_info where  work_id = #{workId} ")
    List<HomeworkInfo> getOtherWork(HomeworkInfo homework);

    @Select("select  class_id as classId,days ,session_number as sessionNumber from homework h where id = #{workId} ")
    Homework getClassIdByWork(String workId);

    @Select("select count(*) from homework  where class_id = #{classId} and   session_number  = #{sessionNumber} and  days = #{days} and id not in (select work_id from student_work  where student_id = #{studentId})")
    int getNotOverWorkNum(String classId,String sessionNumber,String days,String studentId);


 @Select("select count(*) from homework  where class_id = #{classId} and   session_number  = #{sessionNumber}  and id not in (select work_id from student_work  where student_id = #{studentId})")
 int getClassNotOverWorkNum(String classId,String sessionNumber,String studentId);


 @Select("select count(*) from certificate  where student_id = #{studentId} and class_id = #{classId}")
 int getCertificateNum(String studentId,String classId);

 int insertCertificate(Certificate certificate);

 @Update("update student_course set days = days + 1 where student_id = #{studentId} and class_id = #{classId}")
    int upDaysById(String studentId,String classId);

    @Update("update homework_info set like_count = like_count + 1 where id = #{id}")
    int setLikeCount(String id);

    @Select("select id,session_number as sessionNumber,class_id as classId,days,content,title,type  from homework where  class_id = #{classId}  and session_number = #{sessionNumber}")
    List<Homework> getWorksBySession(String classId,String sessionNumber);

    /**
     * 插入评论信息
     * @param comment 评论实体对象
     * @return 插入成功的记录数
     */
    int insertComment(Comment comment);

    /**
     * 根据评论 ID 查询评论信息
     * @param id 评论 ID
     * @return 评论实体对象
     */
    List<Comment> selectCommentById(String id);

    @Select("select id,course_id as courseId,session_number as session_number,session_name as sessionName,video_url as videoUrl from course_session where course_id = #{courseId} and session_number = #{sessionNumber}")
    CourseSession getVideo(String courseId,String  sessionNumber);


    @Update("update course_session set video_url =#{videoUrl}  where course_id = #{courseId} and session_number = #{sessionNumber}")
    int setVideo(String  videoUrl,String courseId,String  sessionNumber);

    @Update("update course_session set start_with =#{startWith} , end_with =#{endWith} where id = #{id} ")
    int setTimes(String  startWith,String endWith,Integer  id);



    @Select("SELECT " +
            "    student_id AS studentId," +
            "    course_id AS courseId," +
            "    course_name AS courseName," +
            "    current_session_number AS currentSessionNumber," +
            "    class_id AS classId," +
            "    class_name AS className," +
            "    large_group_id AS largeGroupId," +
            "    large_group_name AS largeGroupName," +
            "    small_group_id AS smallGroupId," +
            "    small_group_name AS smallGroupName" +
            " FROM " +
            "    student_course " +
            "    where  class_id = #{classId}")
    List<StudentCourse>  getStudentsByClassId(String classId);


    @Select("select * from small_group where id = #{smallGroupId}")
    HashMap<String, String> getSmallGroupById(String smallGroupId);

    @Select("select * from large_group where id = #{smallGroupId}")
    HashMap<String, String> getLargeGroupById(String smallGroupId);

    @Select("select * from small_group where large_group_id in(select id from large_group where class_id = #{classId})")
    List<HashMap<String, String>>  getAllSmallGroups(String classId);


}
